package com.hk.commons;


import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.hk.commons.entity.sys.SysUser;
 

public class ReadExcelToUserDB {
    
    /**
     * read the Excel file
     * @param path the path of the Excel file
     * @return
     * @throws IOException
     */
	
	
	
    public static List<SysUser> readExcel(String path) throws IOException {
        if (StringHelper.isEmpty(path)) {
            return null;
        } else {
            String postfix = getPostfix(path);
            if (StringHelper.isNotEmpty(postfix)) {
                if (Constant.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                    return readXls(path);
                } else if (Constant.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
                    return readXlsx(path);
                }
            } else {
                System.out.println(path + Constant.NOT_EXCEL_FILE);
            }
        }
        return null;
    }

    /**
     * Read the Excel 2010
     * @param path the path of the excel file
     * @return
     * @throws IOException
     */
    public static List<SysUser> readXlsx(String path) throws IOException {
        System.out.println(Constant.PROCESSING + path);
        InputStream is = new FileInputStream(path);
         
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        SysUser user = new SysUser();
        List<SysUser> list = new ArrayList<SysUser>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) { 
                    XSSFCell userName = xssfRow.getCell(0);
                    XSSFCell userPhone = xssfRow.getCell(1);
                    XSSFCell identity = xssfRow.getCell(2);
                    XSSFCell orgId = xssfRow.getCell(3);
                    
                    user.setRealName(getValue(userName));
                    user.setIdentity( getValue(identity));
                    user.setName(getValue(userPhone));
                    user.setOrgId(Integer.valueOf(getValue(orgId)));
                    list.add(user);
                }
            }
        }
        return list;
    }

    /**
     * Read the Excel 2003-2007
     * @param path the path of the Excel
     * @return
     * @throws IOException
     */
    public static List<SysUser> readXls(String path) throws IOException {
        System.out.println(Constant.PROCESSING + path);
        InputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        SysUser user = null;
        List<SysUser> list = new ArrayList<SysUser>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                	user = new SysUser();
                    HSSFCell userName = hssfRow.getCell((short) 0);
                    HSSFCell userPhone = hssfRow.getCell((short) 1);
                    HSSFCell identity = hssfRow.getCell((short) 2);
                    HSSFCell orgId = hssfRow.getCell((short) 3);
                    
                    
                    String realNameString = getValue(userName);
                   
                    user.setRealName(realNameString);
                    
                    String phone = getValue(userPhone);
                    System.out.println("phone num = " + phone);                    
                    user.setName(phone);
                    
                    String identityStr = getValue(identity);
                    System.out.println("identity = " + identityStr);
                    user.setIdentity(identityStr);
                    
                    String orgIdStr = getValue(orgId);
                    System.out.println("orgId = " + orgIdStr);
                    
                    System.out.println("real Name = " + realNameString);
                    if(orgIdStr.indexOf(".") > 0){
                    	System.out.println("before orgIdStr = " + orgIdStr);
                    	orgIdStr = orgIdStr.substring(0, orgIdStr.indexOf("."));
                    	System.out.println("after  orgIdStr = " + orgIdStr);
                    }
                    user.setOrgId(Integer.valueOf(orgIdStr));
                    list.add(user);
                }
            }
        }
        return list;
    }

     
    private static String getValue(XSSFCell xssfRow) {

            return String.valueOf(xssfRow.getStringCellValue()).trim();
    }

    
    private static String getValue(HSSFCell hssfCell) {
    	if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
             // 返回布尔类型的值
             return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
             // 返回数值类型的值
             return String.valueOf(hssfCell.getNumericCellValue());
       } else {
            // 返回字符串类型的值
            return String.valueOf(hssfCell.getRichStringCellValue());
       }
    }
    
    
    public static String getPostfix(String path) {
    	if (StringHelper.isEmpty(path)) {
    	         return null;
    	}
    	 
    	if (path.contains(Constant.POINT)) {
    	         return path.substring(path.lastIndexOf(Constant.POINT) + 1, path.length());
    	}
    	return null;
    }
}
